﻿using Dapper;
using Domain.Entity;
using Domain.Entity.bs;
using Domain.Entity.ord;
using Domain.Entity.sys;
using Domain.IRepository;
using Microsoft.AspNetCore.SignalR;
using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using static Microsoft.EntityFrameworkCore.DbLoggerCategory.Database;

namespace EFCore.Repository
{
    public class StockWarningRepository : IStockWarningRepository
    {
        //连接字符串
        string con = "Data Source=121.37.93.71;Initial Catalog=CSSL_ZCTWMS;User ID=sa;Password=geili@2025;Encrypt=False";


        //检查预警
        public async Task CheckWarning()
        {
            using(SqlConnection sqlConnection=new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                //1.查询所有货品的上下限
                string procsql = $"select * from [dbo].[bs_product] where is_delete=0";
                List<Product> products = sqlConnection.Query<Product>(procsql).ToList();

                //2.查询所有仓库
                string whsql = $"select * from [dbo].[bs_warehouse] where is_delete=0";
                List<Warehouse> warehouses=sqlConnection.Query<Warehouse>(whsql).ToList();


                //3.对每个货品在每个仓库的库存进行检查
                foreach (var product in products)
                {
                    foreach (var warehouse in warehouses)
                    {
                        //查询每个仓库的货品的库存
                        string procstock = $"select SUM(stk_hand_num) as stocknum from [dbo].[bs_stock] where proc_id={product.proc_id} and wh_id={warehouse.wh_id}";
                        int? stocknum=sqlConnection.ExecuteScalar<int?>(procstock);

                        if (stocknum == null)
                        {
                            continue; // 无库存记录，跳过
                        }

                        //检查是否需要预警
                        string warningType = ""; //预警类型
                        string warningMessage = ""; //预警内容

                        //检查库存上下限
                        if (stocknum < product.proc_min)
                        {
                            warningType = "低于下限";
                            warningMessage = $"货品{product.proc_name}在仓库{warehouse.wh_name}中库存不足，低于下限";
                        } 
                        else if (stocknum > product.proc_max)
                        {
                            warningType = "高于上限";
                            warningMessage = $"货品{product.proc_name}在仓库{warehouse.wh_name}中库存过量，高于上限";
                        }

                        //处理预警记录
                        if(!string.IsNullOrEmpty(warningType))
                        {
                            //检查预警表中是否已经有该仓库的货品记录
                            string checksql = $"select count(*) from [dbo].[sys_stock_warning] where proc_id={product.proc_id} and wh_id={warehouse.wh_id}";
                            int count=sqlConnection.ExecuteScalar<int>(checksql);
                            if(count > 0)
                            {
                                //如果有就更新
                                string updatesql = $"update [dbo].[sys_stock_warning] set proc_inventory={stocknum},warning_time='{DateTime.Now}',warning_type='{warningType}' where proc_id={product.proc_id} and wh_id={warehouse.wh_id}";
                                await sqlConnection.ExecuteAsync(updatesql);
                            }
                            else
                            {
                                //没有就新增
                                string insertsql = $@"insert into [dbo].[sys_stock_warning](proc_id, proc_code, proc_name, proc_model, proc_unit, type_name, proc_min, proc_max, wh_id, wh_name, proc_inventory, warning_time, warning_type)
                                                      values({product.proc_id},'{product.proc_code}','{product.proc_name}','{product.proc_model}','{product.proc_primary_unit}','{product.type_name}',{product.proc_min},{product.proc_max},{warehouse.wh_id},'{warehouse.wh_name}',{stocknum},'{DateTime.Now}','{warningType}')";
                                await sqlConnection.ExecuteAsync(insertsql);
                            }
                            //添加信息记录
                            string infosql = $@"insert into [dbo].[sys_info](info_text, info_type, info_status, info_time)
                                              values('{warningMessage}','库存',2,'{DateTime.Now}')";
                            await sqlConnection.ExecuteAsync(infosql);
                        }
                        else
                        {
                            //如果正常就删除记录
                            string desql = $"delete from [dbo].[sys_stock_warning] where proc_id={product.proc_id} and wh_id={warehouse.wh_id}";
                            await sqlConnection.ExecuteAsync(desql);
                        }
                    }
                }
            }
        }


        //到期预警
        public async Task ExpirationWarning()
        {
            using(SqlConnection sqlConnection=new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                // 查询具体的到期货品信息
                string expiredDetailSql = @"
                           SELECT 
                    w.wh_name,
                    p.proc_name,
                    p.proc_code,
                    ind.proc_date,
                    p.proc_expirationdate
                FROM [dbo].[bs_stock] s
                INNER JOIN [dbo].[bs_product] p ON s.proc_id = p.proc_id
                INNER JOIN [dbo].[in_instock_d] ind ON s.proc_id = ind.proc_id
                INNER JOIN [dbo].[bs_warehouse] w ON s.wh_id = w.wh_id
                WHERE p.is_delete = 0
                AND p.is_datamanagement = 1
                AND s.stk_hand_num > 0
                AND ind.proc_date IS NOT NULL
                AND (
                    (p.proc_expirationdate LIKE '%天' AND 
                     ISNUMERIC(REPLACE(p.proc_expirationdate, '天', '')) = 1 AND
                     DATEDIFF(day, ind.proc_date, GETDATE()) > TRY_CAST(REPLACE(p.proc_expirationdate, '天', '') AS INT))
                    OR 
                    (p.proc_expirationdate LIKE '%个月' AND 
                     ISNUMERIC(REPLACE(p.proc_expirationdate, '个月', '')) = 1 AND
                     DATEDIFF(month, ind.proc_date, GETDATE()) > TRY_CAST(REPLACE(p.proc_expirationdate, '个月', '') AS INT))
                    OR 
                    (p.proc_expirationdate LIKE '%年' AND 
                     ISNUMERIC(REPLACE(p.proc_expirationdate, '年', '')) = 1 AND
                     DATEDIFF(year, ind.proc_date, GETDATE()) > TRY_CAST(REPLACE(p.proc_expirationdate, '年', '') AS INT))
                )
                ORDER BY w.wh_name, p.proc_name";


                // 查询具体的临期货品信息
                string nearExpiredDetailSql = @"
                           SELECT 
                    w.wh_name,
                    p.proc_name,
                    p.proc_code,
                    ind.proc_date,
                    p.proc_expirationdate,
                    p.proc_Warningdays,
                    CASE 
                        WHEN p.proc_expirationdate LIKE '%天' AND ISNUMERIC(REPLACE(p.proc_expirationdate, '天', '')) = 1 THEN 
                            CAST(REPLACE(p.proc_expirationdate, '天', '') AS DECIMAL) - DATEDIFF(day, ind.proc_date, GETDATE())
                        WHEN p.proc_expirationdate LIKE '%个月' AND ISNUMERIC(REPLACE(p.proc_expirationdate, '个月', '')) = 1 THEN 
                            DATEDIFF(day, GETDATE(), DATEADD(month, TRY_CAST(REPLACE(p.proc_expirationdate, '个月', '') AS DECIMAL), ind.proc_date))
                        WHEN p.proc_expirationdate LIKE '%年' AND ISNUMERIC(REPLACE(p.proc_expirationdate, '年', '')) = 1 THEN 
                            DATEDIFF(day, GETDATE(), DATEADD(year, TRY_CAST(REPLACE(p.proc_expirationdate, '年', '') AS DECIMAL), ind.proc_date))
                    END AS days_remaining
                FROM [dbo].[bs_stock] s
                INNER JOIN [dbo].[bs_product] p ON s.proc_id = p.proc_id
                INNER JOIN [dbo].[in_instock_d] ind ON s.proc_id = ind.proc_id
                INNER JOIN [dbo].[bs_warehouse] w ON s.wh_id = w.wh_id
                WHERE p.is_delete = 0
                AND p.is_datamanagement = 1
                AND s.stk_hand_num > 0
                AND ind.proc_date IS NOT NULL
                AND (
                    (p.proc_expirationdate LIKE '%天' 
                     AND ISNUMERIC(REPLACE(p.proc_expirationdate, '天', '')) = 1
                     AND TRY_CAST(REPLACE(p.proc_expirationdate, '天', '') AS DECIMAL) IS NOT NULL
                     AND DATEDIFF(day, ind.proc_date, GETDATE()) <= TRY_CAST(REPLACE(p.proc_expirationdate, '天', '') AS DECIMAL)
                     AND (TRY_CAST(REPLACE(p.proc_expirationdate, '天', '') AS DECIMAL) - DATEDIFF(day, ind.proc_date, GETDATE())) <= p.proc_Warningdays)
                    OR 
                    (p.proc_expirationdate LIKE '%个月' 
                     AND ISNUMERIC(REPLACE(p.proc_expirationdate, '个月', '')) = 1
                     AND TRY_CAST(REPLACE(p.proc_expirationdate, '个月', '') AS DECIMAL) IS NOT NULL
                     AND DATEDIFF(month, ind.proc_date, GETDATE()) <= TRY_CAST(REPLACE(p.proc_expirationdate, '个月', '') AS DECIMAL)
                     AND DATEDIFF(day, GETDATE(), DATEADD(month, TRY_CAST(REPLACE(p.proc_expirationdate, '个月', '') AS DECIMAL), ind.proc_date)) <= p.proc_Warningdays)
                    OR
                    (p.proc_expirationdate LIKE '%年' 
                     AND ISNUMERIC(REPLACE(p.proc_expirationdate, '年', '')) = 1
                     AND TRY_CAST(REPLACE(p.proc_expirationdate, '年', '') AS DECIMAL) IS NOT NULL
                     AND DATEDIFF(year, ind.proc_date, GETDATE()) <= TRY_CAST(REPLACE(p.proc_expirationdate, '年', '') AS DECIMAL)
                     AND DATEDIFF(day, GETDATE(), DATEADD(year, TRY_CAST(REPLACE(p.proc_expirationdate, '年', '') AS DECIMAL), ind.proc_date)) <= p.proc_Warningdays)
                )
                ORDER BY w.wh_name, p.proc_name";

                // 执行查询
                var expiredProducts = await sqlConnection.QueryAsync(expiredDetailSql);
                var nearExpiredProducts = await sqlConnection.QueryAsync(nearExpiredDetailSql);

                // 处理到期货品
                foreach (var product in expiredProducts)
                {
                    string message = $"【{product.wh_name}】仓库的货品【{product.proc_name}({product.proc_code})】已到期，请及时处理";

                    await InsertInfoRecord(sqlConnection, message, "到期提醒");
                }

                // 处理临期货品
                foreach (var product in nearExpiredProducts)
                {
                    string message = $"【{product.wh_name}】仓库的货品【{product.proc_name}({product.proc_code})】即将到期，请注意处理";

                    await InsertInfoRecord(sqlConnection, message, "到期提醒");
                }

                Console.WriteLine($"到期预警检查完成 - 到期货品: {expiredProducts.Count()}, 临期货品: {nearExpiredProducts.Count()}");

            }
        }

        // 插入到期信息记录
        private async Task InsertInfoRecord(SqlConnection connection, string message, string infoType)
        {
            string insertSql = @"INSERT INTO [dbo].[sys_info](info_text, info_type, info_status, info_time)
                         VALUES(@info_text, @info_type, @info_status, @info_time)";

            await connection.ExecuteAsync(insertSql, new
            {
                info_text = message,
                info_type = infoType,
                info_status = 2, // 未读状态
                info_time = DateTime.Now
            });
        }


        //分页查询
        public async Task<FenYe<StockWarning>> GetStockWarning(FenYe<StockWarning> fenYe, string? name, string? type, string? warning, int? startnum, int? endnum)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();

                // 分页存储过程
                DynamicParameters param = new DynamicParameters();
                param.Add("@pageSize", 10);
                param.Add("@keyName", "sw_id");
                param.Add("@tabelName", "sys_stock_warning");
                param.Add("@currentPage", fenYe.CurrentPage);
                param.Add("@rows", dbType: DbType.Int32, direction: ParameterDirection.Output);
                //判断条件
                string sqlparam = $" 1=1 ";
                if (!string.IsNullOrEmpty(name))
                    sqlparam += $" and proc_name like '%{name}%'";
                if (!string.IsNullOrEmpty(type))
                    sqlparam += $" and type_name like '%{type}%'";
                if (!string.IsNullOrEmpty(warning))
                    sqlparam += $" and warning_type like '%{warning}%'";
                if ((startnum!=0&&startnum!=null)|| (endnum != 0 && endnum != null))
                    sqlparam += $" and  proc_inventory between '{startnum}' and '{endnum}'";

                param.Add("@where", sqlparam);

                string sql = "exec [dbo].[Fenye] @pageSize, @keyName, @tabelName, @where, @currentPage, @rows output";
                List<StockWarning> list = sqlConnection.Query<StockWarning>(sql, param).ToList();
                fenYe.List = list;
                fenYe.Rows = param.Get<int>("@rows");
                return fenYe;
            }
        }
    }
}
